//
//  DataBaseManager.m
//  HappinessTrip
//
//  Created by dllo on 15/10/19.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBaseManager.h"

//创建数据库对象
static sqlite3 *dbPoint = nil;

@implementation DataBaseManager

+(void)openDataBase
{
    if (dbPoint) {
        return;
    }
    //创建数据库文件
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    path = [path stringByAppendingPathComponent:@"MyDataBase.sqlite"];
    //执行sqlite的打开数据库函数
    sqlite3_open(path.UTF8String, &dbPoint);
    
    NSLog(@"%@", path);
    
}

+(void)closeDataBase
{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
        dbPoint = nil;
    }
    else {
        NSLog(@"关闭数据库失败");
    }
}

+(void)createPlayTable
{
    [self openDataBase];
    NSString *sqlString = @"CREATE TABLE IF NOT EXISTS PLAY(product_id VARCHAR(20) PRIMARY KEY , address VARCHAR(100), like_count VARCHAR(100), price VARCHAR(100), tab_list VARCHAR(100), title VARCHAR(100), title_page VARCHAR(100), avatar_l VARCHAR(100))";
    //执行SQL语句
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"表格创建成功");
    }else{
        NSLog(@"表格创建失败");
    }
        NSLog(@"creat = %d", result);
    [self closeDataBase];

}

+(void)insertPlayList:(PlayList *)playList
{
    NSString *tablist = [playList.tab_list componentsJoinedByString:@"  "];
    [self openDataBase];
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO PLAY(product_id, address, like_count, price, tab_list, title, title_page, avatar_l) VALUES('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", playList.product_id, playList.address, playList.like_count, playList.price, tablist, playList.title , playList.title_page, [playList.user objectForKey:@"avatar_l"]];
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败");
    }
        NSLog(@"insert = %d", result);
    [self closeDataBase];

}


+(void)deletePlayList:(NSString *)product_id
{
    [self openDataBase];
    NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM PLAY WHERE product_id = '%@'", product_id];
    //执行SQL语句
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    [self closeDataBase];
}




+(NSMutableArray *)selectPlayList
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM PLAY"];
    //执行sql语句
    
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    //创建一个数值来存放查询后的对象
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"查找成功");
        //遍历符合条件的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //如果下一行有数据,就去读取;
            const unsigned char *product_id = sqlite3_column_text(stmt, 0);
            const unsigned char *address = sqlite3_column_text(stmt, 1);
            const unsigned char *like_count = sqlite3_column_text(stmt, 2);
            const unsigned char *price = sqlite3_column_text(stmt, 3);
            const unsigned char *tab_list = sqlite3_column_text(stmt, 4);
            const unsigned char *title = sqlite3_column_text(stmt, 5);
            const unsigned char *title_page = sqlite3_column_text(stmt, 6);
            const unsigned char *avatar_l = sqlite3_column_text(stmt, 7);
            //对读出的信息进行转义
            NSString *playProduct_id = [NSString stringWithUTF8String:(const char*)product_id];
            NSString *playAddress = [NSString stringWithUTF8String:(const char*)address];
            NSString *playLike_count = [NSString stringWithUTF8String:(const char*)like_count];
            NSString *playPrice = [NSString stringWithUTF8String:(const char*)price];
            NSString *playTab_list = [NSString stringWithUTF8String:(const char*)tab_list];
            NSString *playTitle = [NSString stringWithUTF8String:(const char*)title];
            NSString *playTitle_page = [NSString stringWithUTF8String:(const char*)title_page];
            NSString *playAvatar_l = [NSString stringWithUTF8String:(const char*)avatar_l];
            //将四条数据封装成对象
            PlayList *play = [[PlayList alloc] init];
            play.product_id = playProduct_id;
            play.address = playAddress;
            play.like_count = playLike_count;
            play.price = playPrice;
            NSArray *playArray = [playTab_list componentsSeparatedByString:@"  "];
            play.tab_list = playArray;
            play.title = playTitle;
            play.title_page = playTitle_page;
            play.user = [NSDictionary dictionaryWithObjectsAndKeys:playAvatar_l, @"avatar_l", nil];
            [array addObject:play];
        }
    }else{
        NSLog(@"查询失败");
    }
    //关闭数据库
    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;

}

+(NSMutableArray *)selectAllPlayByMovieName:(NSString *)title
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    
    NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM PLAY WHERE title LIKE '%%%@%%'", title];
    NSMutableArray *array = [NSMutableArray array];
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *product_id = sqlite3_column_text(stmt, 0);
            const unsigned char *address = sqlite3_column_text(stmt, 1);
            const unsigned char *like_count = sqlite3_column_text(stmt, 2);
            const unsigned char *price = sqlite3_column_text(stmt, 3);
            const unsigned char *tab_list = sqlite3_column_text(stmt, 4);
            const unsigned char *title = sqlite3_column_text(stmt, 5);
            const unsigned char *title_page = sqlite3_column_text(stmt, 6);
            const unsigned char *avatar_l = sqlite3_column_text(stmt, 7);
            NSString *playProduct_id = [NSString stringWithUTF8String:(const char*)product_id];
            NSString *playAddress = [NSString stringWithUTF8String:(const char*)address];
            NSString *playLike_count = [NSString stringWithUTF8String:(const char*)like_count];
            NSString *playPrice = [NSString stringWithUTF8String:(const char*)price];
            NSString *playTab_list = [NSString stringWithUTF8String:(const char*)tab_list];
            NSString *playTitle = [NSString stringWithUTF8String:(const char*)title];
            NSString *playTitle_page = [NSString stringWithUTF8String:(const char*)title_page];
            NSString *playAvatar_l = [NSString stringWithUTF8String:(const char*)avatar_l];
            PlayList *play = [[PlayList alloc] init];
            play.product_id = playProduct_id;
            play.address = playAddress;
            play.like_count = playLike_count;
            play.price = playPrice;
            NSArray *playArray = [playTab_list componentsSeparatedByString:@"  "];
            play.tab_list = playArray;
            play.title = playTitle;
            play.title_page = playTitle_page;
            play.user = [NSDictionary dictionaryWithObjectsAndKeys:playAvatar_l, @"avatar_l", nil];
            [array addObject:play];
        }
    }
    else
    {
        NSLog(@"查询失败:%d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;

}













@end
